USE [ventas]
GO

/****** Object:  View [saventa].[vw_lotes]    Script Date: 08/16/2012 15:52:51 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[saventa].[vw_lotes]'))
DROP VIEW [saventa].[vw_lotes]
GO

USE [ventas]
GO

/****** Object:  View [saventa].[vw_lotes]    Script Date: 08/16/2012 15:52:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE VIEW [saventa].[vw_lotes] AS (
SELECT   
vl.lot_urb AS Codigo_Urbanizacion, 
vl.lot_pro AS Codigo_proyecto, 
vl.lot_id AS Codigo_lote, 
vl.lot_mz AS Manzana, 
vl.lot_num AS Lote, 
va.are_val AS area_terreno, 
vp1.par_valor AS plazo_entrega, 
vp2.par_valor AS plazo_entrada
FROM         ventas.saventa.tb_ven_lot AS vl LEFT OUTER JOIN
                      ventas.saventa.tb_ven_parneg AS vp1 ON vp1.pro_id = vl.lot_id AND vp1.par_tipo = '00020' AND vp1.tipo_fin = 'CV' LEFT OUTER JOIN
                      ventas.saventa.tb_ven_parneg AS vp2 ON vp2.pro_id = vl.lot_id AND vp2.par_tipo = '00003' AND vp2.tipo_fin = 'CV' LEFT OUTER JOIN
                      ventas.saventa.tb_ven_area AS va ON va.are_tip = '00006' AND vl.lot_id = va.lot_id
WHERE     (vl.lot_sta = '00001')
and vl.lot_pro NOT IN (select urb_id from ventas.saventa.tb_ven_urb_config where
parametro_id = 'CONVIVENCIAPROYECTO' and parametro_valor = 'S')

union

select
CP.ID AS Codigo_Urbanizacion,
IVPL.pro_id AS Codigo_proyecto,
IVPL.lot_id AS Codigo_lote,
IVPI.IdUbicacion1 AS Manzana,
IVPI.IdUbicacion3 AS Lote,
IVPI.Area AS area_terreno,
IVPIPN.PlazoEntrega as plazo_entrega,
IVPIPN.PlazoCuotaEntrada AS plazo_entrega
from [premedusa\sql2008].Data.InventarioVentas.ProductoLote IVPL
INNER JOIN [premedusa\sql2008].Data.InventarioVentas.ProductoInventariable IVPI
	ON IVPL.IdProducto=IVPI.IdProductoInventariable
	and IVPL.pro_id IS NOT NULL
	and IVPI.IdEstadoComercial=1
INNER JOIN  [premedusa\sql2008].Data.Configuracion.Proyecto CP
	ON IVPI.IdPersonaPromotor=CP.IdEmpresa
INNER JOIN  [premedusa\sql2008].Data.InventarioVentas.ProductoInventariableParametroNegociacion IVPIPN
	on IVPL.IdProducto=IVPIPN.IdProductoInventariable
	and IVPIPN.IdTipoFinanciamiento='C'
	and IVPIPN.VenderComo=1
where IVPL.pro_id IN (select urb_id from ventas.saventa.tb_ven_urb_config where
parametro_id = 'CONVIVENCIAPROYECTO' and parametro_valor = 'S')
)







GO


